package com.glowdayz.glowmee.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.DateTime;
import com.glowdayz.common.PageHandler;
import com.glowdayz.common.MultipartBox.FileInfo;
import com.glowdayz.glowmee.cmd.ProductCMD;
import com.glowdayz.glowmee.cmd.ProductCategoryCMD;

/** 제품 테이블 기능을 구현하는 클래스 */
public class ProductDAO {
	
	static Logger logger = Logger.getLogger(ProductDAO.class);
	
	private static ProductDAO instance = null;
	
	DBHandler db = null;
	
	public static ProductDAO getInstance() {
		if (instance == null) {
			instance = new ProductDAO();
		}
		return instance;
	}
	
	public ProductDAO() {
		db = DBHandler.getInstance();
	}
	
	/**
	 * 제품 추가용
	 * @param cmd
	 * @return int
	 * @throws SQLException 
	 */
	public int insertProduct(ProductCMD cmd, ArrayList<ProductCategoryCMD> cateList) throws SQLException {
		logger.debug("[insertProduct] ---------- Method Start ----------");
		logger.debug("[insertProduct][PARAM] ProductCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		PreparedStatement pstmt = null;
		
		String sql = "INSERT INTO product(idBrand, productTitle, volume, price, productText, colorType, keyWord, "
				+ "readCount, productScore, ratingAvg, rationCount, isDisplay, create_date"
				+ ") VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0, 0, 0, 1, ?)";
		
		DBHandler.getInstance().beginTrans();
		
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIdBrand());
			pstmt.setString(2, cmd.getProductTitle());
			pstmt.setString(3, cmd.getVolume());
			pstmt.setInt(4, cmd.getPrice());
			pstmt.setString(5, cmd.getProductText());
			pstmt.setString(6, cmd.getColorType());
			pstmt.setString(7, cmd.getKeyWord());
			pstmt.setString(8, DateTime.getInstance().getNowDateTimeString());
			result = pstmt.executeUpdate();
			
			if (result > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
			
			String cateSql = "INSERT INTO secondcategorymapping ("
					+ "idProduct, idSecondCategory"
					+ ") VALUES (?, ?)";
			for (int i=0; i<cateList.size(); i++) {
				ProductCategoryCMD cateCMD = cateList.get(i);
				pstmt = db.getPrepareStatement(cateSql);
				
				pstmt.setInt(1, result);
				pstmt.setInt(2, cateCMD.getIdSecondCategory());
				
				pstmt.executeUpdate();
			}
			pstmt.close();
			pstmt = null;
			
			if (cmd.getImage() != null) {
				
				String fileSql = "INSERT INTO attachefile ("
						+ "tableName, tableKey, fileOrgName, fileSaveName, fileDir, fileSize, fileType, isDisplay, create_date, isUser"
						+ ") VALUES ('product', ?, ?, ?, ?, ?, ?, 1, ?, 0)";
				
				int fileResult = 0;
				
				FileInfo item = cmd.getImage();
				
				pstmt = db.getPrepareStatement(fileSql);
				pstmt.setInt(1, result);
				pstmt.setString(2, item.getFileOrgName());
				pstmt.setString(3, item.getFileSaveName());
				pstmt.setString(4, item.getFileDir());
				pstmt.setLong(5, item.getFileSize());
				pstmt.setString(6, item.getFileType());
				pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
				
				fileResult = pstmt.executeUpdate();
				
				if (fileResult > 0) {
					// 자동증가 일련변호 조회
					ResultSet rs = pstmt.getGeneratedKeys();
					if (rs.next()) {
						fileResult = rs.getInt(1);
					}
					rs.close();
					rs = null;
				} else {
					// 모든 데이터 저장 취소
					pstmt.close();
					pstmt = null;
					DBHandler.getInstance().rollback();
					return -1;
				}
				
				pstmt.close();
				pstmt = null;
				
				String update = "UPDATE product "
						+ "SET productImg=? "
						+ "WHERE idProduct=?";
				pstmt = db.getPrepareStatement(update);
				pstmt.setInt(1, fileResult);
				pstmt.setInt(2, result);
				result = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertProduct] " + e.getLocalizedMessage());
			logger.error("[insertProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertProduct][RETURN] result=" + result);
		logger.debug("[insertProduct] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 제품 전시여부 수정
	 * @param cmd
	 * @return int
	 */
	public int unActivityProduct(ProductCMD cmd) throws SQLException {
		logger.debug("[unActivityProduct] ---------- Method Start ----------");
		logger.debug("[unActivityProduct][PARAM] ProductCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		String sql = "UPDATE product "
				+ "SET isDisplay=?, modified_date=? "
				+ "WHERE idProduct=?";
		PreparedStatement pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIsDisplay());
			pstmt.setString(2, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(3, cmd.getIdProduct());
			
			DBHandler.getInstance().beginTrans();
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[unActivityProduct] " + e.getLocalizedMessage());
			logger.error("[unActivityProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[unActivityProduct][RETURN] result=" + result);
		logger.debug("[unActivityProduct] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 제품 수정
	 * @param cmd
	 * @param cateList
	 * @param delete_file
	 * @return int
	 * @throws SQLException 
	 */
	public int updateProduct(ProductCMD cmd, ArrayList<ProductCategoryCMD> cateList) throws SQLException {
		logger.debug("[updateProduct] ---------- Method Start ----------");
		logger.debug("[updateProduct][PARAM] ProductCMD cmd=" + cmd.toString());
		logger.debug("[updateProduct][PARAM] ArrayList<ProductCategoryCMD> cateList=" + cateList.size());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		PreparedStatement pstmt = null;
		
		// (3) 게시물 정보 갱신
		String sql = "UPDATE product "
				+ "SET idBrand=?, productTitle=?, volume=?, price=?, productText=?, colorType=?, keyWord=?, isDisplay=?, modified_date=? "
				+ "WHERE idProduct=?";
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, cmd.getIdBrand());
			pstmt.setString(2, cmd.getProductTitle());
			pstmt.setString(3, cmd.getVolume());
			pstmt.setInt(4, cmd.getPrice());
			pstmt.setString(5, cmd.getProductText());
			pstmt.setString(6, cmd.getColorType());
			pstmt.setString(7, cmd.getKeyWord());
			pstmt.setInt(8, cmd.getIsDisplay());
			pstmt.setString(9, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(10, cmd.getIdProduct());
			
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
			
			if (cmd.getImage() != null) {
				
				String fileSql = "INSERT INTO attachefile ("
						+ "tableName, tableKey, fileOrgName, fileSaveName, fileDir, fileSize, fileType, isDisplay, create_date, isUser"
						+ ") VALUES ('product', ?, ?, ?, ?, ?, ?, 1, ?, 0)";
				
				int fileResult = 0;
				
				FileInfo item = cmd.getImage();
				
				pstmt = db.getPrepareStatement(fileSql);
				pstmt.setInt(1, cmd.getIdProduct());
				pstmt.setString(2, item.getFileOrgName());
				pstmt.setString(3, item.getFileSaveName());
				pstmt.setString(4, item.getFileDir());
				pstmt.setLong(5, item.getFileSize());
				pstmt.setString(6, item.getFileType());
				pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
				
				fileResult = pstmt.executeUpdate();
				
				if (fileResult > 0) {
					// 자동증가 일련변호 조회
					ResultSet rs = pstmt.getGeneratedKeys();
					if (rs.next()) {
						fileResult = rs.getInt(1);
					}
					rs.close();
					rs = null;
				} else {
					// 모든 데이터 저장 취소
					pstmt.close();
					pstmt = null;
					DBHandler.getInstance().rollback();
					return -1;
				}
				
				pstmt.close();
				pstmt = null;
				
				String update = "UPDATE product "
						+ "SET productImg=? "
						+ "WHERE idProduct=?";
				pstmt = db.getPrepareStatement(update);
				pstmt.setInt(1, fileResult);
				pstmt.setInt(2, cmd.getIdProduct());
				result = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			}
			
			String beforCate = "SELECT idSecondCategory FROM secondcategorymapping WHERE idProduct=?";
			pstmt = DBHandler.getInstance().getPrepareStatement(beforCate);
			ArrayList<ProductCategoryCMD> cate_list = new ArrayList<ProductCategoryCMD>();
			pstmt.setInt(1, cmd.getIdProduct());
			ResultSet cateRs = pstmt.executeQuery();
			if (cateRs != null) {
				while(cateRs.next()) {
					int idSecondCategory = cateRs.getInt(1);
					
					ProductCategoryCMD temp = new ProductCategoryCMD(idSecondCategory);
					cate_list.add(temp);
				}
				cateRs.close();
				cateRs = null;
			}
			pstmt.close();
			pstmt = null;

			for (int i=0; i<cate_list.size(); i++) {
				ProductCategoryCMD temp = cate_list.get(i);
				boolean isCate = false;
				for (int j=0; j<cateList.size(); j++) {
					ProductCategoryCMD temp2 = cateList.get(j);
					if (temp.getIdSecondCategory() == temp2.getIdSecondCategory()) {
						cateList.remove(j);
						isCate = true;
					}
				}
				if (isCate == false) {
					String del_cate = "DELETE FROM secondcategorymapping WHERE idSecondCategory=? AND idProduct=?";
					pstmt = DBHandler.getInstance().getPrepareStatement(del_cate);
					pstmt.setInt(1, temp.getIdSecondCategory());
					pstmt.setInt(2, cmd.getIdProduct());
					pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				}
			}
			
			if (cateList.size() > 0) {
				String addCate = "INSERT INTO secondcategorymapping ("
						+ "idProduct, idSecondCategory"
						+ ") VALUES (?, ?)";
				for (int i=0; i<cateList.size(); i++) {
					ProductCategoryCMD cateCMD = cateList.get(i);
					pstmt = db.getPrepareStatement(addCate);
					
					pstmt.setInt(1, cmd.getIdProduct());
					pstmt.setInt(2, cateCMD.getIdSecondCategory());
					
					pstmt.executeUpdate();
				}
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateProduct] " + e.getLocalizedMessage());
			logger.error("[updateProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateProduct][RETURN] result=" + result);
		logger.debug("[updateProduct] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 제품 리스트 카테고리용
	 * @param idProduct
	 * @return ArrayList<ProductCategoryCMD>
	 */
	public ArrayList<ProductCategoryCMD> getCategoryList(int idProduct) throws SQLException {
		
		ArrayList<ProductCategoryCMD> result = null;
		
		String sql = "SELECT m.idSecondCategory, s.secondCategoryText, f.idFirstCategory, f.firstCategoryText "
				+ "FROM secondcategorymapping m INNER JOIN product p ON m.idProduct = p.idProduct "
				+ "INNER JOIN secondcategory s ON m.idSecondCategory = s.idSecondCategory "
				+ "INNER JOIN firstcategory f ON s.idFirstCategory = f.idFirstCategory "
				+ "WHERE m.idProduct = ?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idProduct);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<ProductCategoryCMD>();
				
				while(rs.next()) {
					int idSecondCategory = rs.getInt(1);
					String idSecondCategoryValue = rs.getString(2);
					int idFirstCategory = rs.getInt(3);
					String idFirstCategoryValue = rs.getString(4);
					ProductCategoryCMD item = new ProductCategoryCMD(idFirstCategory, idFirstCategoryValue, 
							idSecondCategory, idSecondCategoryValue);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getProductList] " + e.getLocalizedMessage());
			logger.error("[getProductList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getProductList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getProductList] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 제품 조회용
	 * @param idProduct
	 * @return ProductCMD
	 */
	public ProductCMD getProduct(int idProduct) throws SQLException {
		logger.debug("[getProduct] ---------- Method Start ----------");
		logger.debug("[getProduct][PARAM] idProduct=" + idProduct);
		
		ProductCMD result = null;
		String sql = "SELECT idProduct, idBrand, productTitle, volume, price, productText, colorType, keyWord, readCount, productScore, "
				+ "ratingAvg, rationCount, isDisplay, create_date, (SELECT COUNT(*) FROM wishlistmapping w WHERE w.idProduct=product.idProduct) "
				+ "FROM product "
				+ "WHERE idProduct=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idProduct);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					int idBrand = rs.getInt(2);
					String productTitle = rs.getString(3);
					String volume = rs.getString(4);
					int price =rs.getInt(5);
					String productText = rs.getString(6);
					String colorType = rs.getString(7);
					String keyWord = rs.getString(8);
					int readCount = rs.getInt(9);
					int productScore = rs.getInt(10);
					Double ratingAvg = rs.getDouble(11);
					int rationCount = rs.getInt(12);
					int isDisplay = rs.getInt(13);
					String create_date = rs.getString(14);
					int wishCount = rs.getInt(15);
					
					ArrayList<ProductCategoryCMD> cateList = getCategoryList(num);
					FileInfo image = this.getImage(num, "product");
					
					result = new ProductCMD(idProduct, idBrand, productTitle, volume, price, productText, colorType, keyWord, readCount,
							productScore, ratingAvg, rationCount, isDisplay, create_date, wishCount, image, cateList);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getProduct] " + e.getLocalizedMessage());
			logger.error("[getProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getProduct][RETURN] result=" + result.toString());
		}
		logger.debug("[getProduct] ---------- Method End ----------");
		
		return result;
	}
	
	/** 제품정보 xml */
	public ProductCMD getProductXml(int idProduct) throws SQLException {
		logger.debug("[getProduct] ---------- Method Start ----------");
		logger.debug("[getProduct][PARAM] idProduct=" + idProduct);
		
		PreparedStatement pstmt = null;
		
		String update = "UPDATE product "
				+ "SET readCount=(readCount+1) "
				+ "WHERE idProduct=?";
		
		pstmt = db.getPrepareStatement(update);
		
		try {
			pstmt.setInt(1, idProduct);
			pstmt.executeUpdate();
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[getProduct] " + e.getLocalizedMessage());
			logger.error("[getProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		ProductCMD result = null;
		String sql = "SELECT p.idProduct, p.idBrand, b.brandTitle, p.productTitle, p.volume, p.price, p.productText, p.colorType, p.keyWord, p.ratingAvg,"
				+ "(SELECT COUNT(*) FROM reviewcomment r WHERE r.idProduct=p.idProduct AND r.isDisplay=1) rationCount, "
				+ "(SELECT COUNT(*) FROM reviewcomment r WHERE r.idProduct=p.idProduct AND r.isDisplay=1 AND r.rating=1), "
				+ "(SELECT COUNT(*) FROM reviewcomment r WHERE r.idProduct=p.idProduct AND r.isDisplay=1 AND r.rating=2), "
				+ "(SELECT COUNT(*) FROM reviewcomment r WHERE r.idProduct=p.idProduct AND r.isDisplay=1 AND r.rating=3), "
				+ "(SELECT COUNT(*) FROM reviewcomment r WHERE r.idProduct=p.idProduct AND r.isDisplay=1 AND r.rating=4), "
				+ "(SELECT COUNT(*) FROM reviewcomment r WHERE r.idProduct=p.idProduct AND r.isDisplay=1 AND r.rating=5) "
				+ "FROM product p, brand b "
				+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND p.idProduct=? "
				+ "GROUP BY p.idProduct";
		pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			pstmt.setInt(1, idProduct);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				if (rs.next()) {
					int num = rs.getInt(1);
					int idBrand = rs.getInt(2);
					String brandTitle = rs.getString(3);
					String productTitle = rs.getString(4);
					String volume = rs.getString(5);
					int price =rs.getInt(6);
					String productText = rs.getString(7);
					String colorType = rs.getString(8);
					String keyWord = rs.getString(9);
					Double ratingAvg = rs.getDouble(10);
					int rationCount = rs.getInt(11);
					int rating1 = rs.getInt(12);
					int rating2 = rs.getInt(13);
					int rating3 = rs.getInt(14);
					int rating4 = rs.getInt(15);
					int rating5 = rs.getInt(16);
					
					FileInfo image= this.getImage(idBrand, "brand");
					FileInfo productImg = this.getImage(num, "product");
					
					result = new ProductCMD(idProduct, idBrand, brandTitle, productTitle, volume, price, productText, colorType, keyWord,
							ratingAvg, rationCount, image, productImg, rating1, rating2, rating3, rating4, rating5);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getProduct] " + e.getLocalizedMessage());
			logger.error("[getProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getProduct][RETURN] result=" + result.toString());
		}
		logger.debug("[getProduct] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 리스트용
	 * @param page
	 * @param count
	 * @param order
	 * @param where
	 * @return ArrayList<ProductCMD>
	 */
	public ArrayList<ProductCMD> getProductList(int page, int count, String order, String where) throws SQLException {
		logger.debug("[getProductList] ---------- Method Start ----------");
		logger.debug("[getProductList][PARAM] page=" + page);
		logger.debug("[getProductList][PARAM] count=" + count);
		logger.debug("[getProductList][PARAM] order=" + order);
		logger.debug("[getProductList][PARAM] where=" + where);
		
		ArrayList<ProductCMD> result = null;
		int cnt;
		if (where == null) {
			cnt = db.getCount("product", "idProduct", null);
		} else {
			cnt = db.getCount("product p", "idProduct", "idProduct is not null" + where);
		}
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
			return null;
		}
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = null;
		if (order == null && where == null) {
			sql = "SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.readCount, "
					+ "(SELECT COUNT(*) FROM wishlistmapping w WHERE w.idProduct=p.idProduct) count1, "
					+ "(SELECT COUNT(*) FROM pouchproductmapping m WHERE m.idProduct=p.idProduct) count2, "
					+ "(SELECT COUNT(*) FROM reviewcomment r WHERE r.idProduct=p.idProduct) count3, "
					+ "p.create_date, p.modified_date, p.isDisplay, p.price, p.productScore, p.keyWord "
					+ "FROM product p INNER JOIN brand b ON p.idBrand = b.idBrand "
					+ "GROUP BY p.idProduct ORDER BY p.idProduct DESC "
					+ "LIMIT ?, ?";
		} else if (order != null && where != null) {
			sql = "SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.readCount, "
					+ "(SELECT COUNT(*) FROM wishlistmapping w WHERE w.idProduct=p.idProduct) count1, "
					+ "(SELECT COUNT(*) FROM pouchproductmapping m WHERE m.idProduct=p.idProduct) count2, "
					+ "(SELECT COUNT(*) FROM reviewcomment r WHERE r.idProduct=p.idProduct) count3, "
					+ "p.create_date, p.modified_date, p.isDisplay, p.price, p.productScore, p.keyWord "
					+ "FROM product p INNER JOIN brand b ON p.idBrand = b.idBrand WHERE p.idProduct is not null" + where + " "
					+ "GROUP BY p.idProduct ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (order != null) {
			sql = "SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.readCount, "
					+ "(SELECT COUNT(*) FROM wishlistmapping w WHERE w.idProduct=p.idProduct) count1, "
					+ "(SELECT COUNT(*) FROM pouchproductmapping m WHERE m.idProduct=p.idProduct) count2, "
					+ "(SELECT COUNT(*) FROM reviewcomment r WHERE r.idProduct=p.idProduct) count3, "
					+ "p.create_date, p.modified_date, p.isDisplay, p.price, p.productScore, p.keyWord "
					+ "FROM product p INNER JOIN brand b ON p.idBrand = b.idBrand "
					+ "GROUP BY p.idProduct ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (where != null) {
			sql = "SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.readCount, "
					+ "(SELECT COUNT(*) FROM wishlistmapping w WHERE w.idProduct=p.idProduct) count1, "
					+ "(SELECT COUNT(*) FROM pouchproductmapping m WHERE m.idProduct=p.idProduct) count2, "
					+ "(SELECT COUNT(*) FROM reviewcomment r WHERE r.idProduct=p.idProduct) count3, "
					+ "p.create_date, p.modified_date, p.isDisplay, p.price, p.productScore, p.keyWord "
					+ "FROM product p INNER JOIN brand b ON p.idBrand = b.idBrand WHERE p.idProduct is not null" + where + " "
					+ "GROUP BY p.idProduct ORDER BY p.idProduct DESC "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<ProductCMD>();
				
				while(rs.next()) {
					int idProduct = rs.getInt(1);
					String productTitle = rs.getString(2);
					int idBrand = rs.getInt(3);
					String brandTitle = rs.getString(4);
					int readCount = rs.getInt(5);
					int wishCount = rs.getInt(6);
					int pouchCount = rs.getInt(7);
					int commentCount = rs.getInt(8);
					String create_date = rs.getString(9);
					String modified_date = rs.getString(10);
					int isDisplay = rs.getInt(11);
					int price = rs.getInt(12);
					int productScore = rs.getInt(13);
					
					productTitle = productTitle.replace("&", "&amp;");
					
					ArrayList<ProductCategoryCMD> cateList = getCategoryList(idProduct);
					FileInfo productImg = this.getImage(idProduct, "product");
					
					ProductCMD item = new ProductCMD(idProduct, idBrand, brandTitle, productTitle, readCount, isDisplay, create_date, modified_date, 
							wishCount, pouchCount, commentCount, cateList, price, productScore, productImg);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getProductList] " + e.getLocalizedMessage());
			logger.error("[getProductList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getProductList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getProductList] ---------- Method End ----------");
		
		return result;
	}
	
	public ArrayList<ProductCMD> getProductImg(int[] productArr) throws SQLException {
		logger.debug("[getProductImg] ---------- Method Start ----------");
		
		ArrayList<ProductCMD> result = new ArrayList<ProductCMD>();
		
		for (int i=0; i<productArr.length; i++) {
			if (productArr[i] > 0) {
				FileInfo productImg = this.getImage(productArr[i], "product");
				ProductCMD item = new ProductCMD(productArr[i], productImg);
				result.add(item);
			}
		}
		
		logger.debug("[getProductImg] ---------- Method End ----------");
		return result;
	}
	
	/** 제품 이름 */
	public ArrayList<String> getProductTitleList(String typehead) throws SQLException {
		logger.debug("[getProductTitleList] ---------- Method Start ----------");
		
		ArrayList<String> result = null;
		String sql = "SELECT productTitle FROM product WHERE replace(productTitle,' ','') like '%" + typehead + "%'";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		try {
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<String>();
				
				while(rs.next()) {
					String productTitle = rs.getString(1);
					result.add(productTitle);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getProductTitleList] " + e.getLocalizedMessage());
			logger.error("[getProductTitleList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		logger.debug("[getProductTitleList] ---------- Method End ----------");
		return result;
	}
	
	/** 제품 리스트 xml */
	public ArrayList<ProductCMD> getProductList(int page, int count, String type, int id, String order, String where) throws SQLException {
		logger.debug("[getProductList] ---------- Method Start ----------");
		
		ArrayList<ProductCMD> result = null;
		int cnt = 0;
		if (type.equals("category")) {
			cnt = db.getCount("(SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.volume, p.price, p.ratingAvg, p.rationCount "
					+ "FROM product p, brand b, secondcategorymapping m "
					+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND m.idProduct=p.idProduct AND m.idSecondCategory=" + id + where + " "
					+ "GROUP BY p.idProduct) tbl",
					"*", null);
		} else if (type.equals("brand")) {
			cnt = db.getCount("(SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.volume, p.price, p.ratingAvg, p.rationCount "
					+ "FROM product p, brand b, secondcategorymapping m, firstcategory f, secondcategory s "
					+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND m.idProduct=p.idProduct AND m.idSecondCategory=s.idSecondCategory AND "
					+ "s.idFirstCategory=f.idFirstCategory AND b.idBrand=" + id + where + " "
					+ "GROUP BY p.idProduct) tbl", 
					"*", null);
		} else if (type.equals("search")) {
			cnt = db.getCount("(SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.volume, p.price, p.ratingAvg, p.rationCount "
					+ "FROM product p, brand b, secondcategorymapping m "
					+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND m.idProduct=p.idProduct" + where + " "
					+ "GROUP BY p.idProduct) tbl", 
					"*", null);
		} else if (type.equals("wish")) {
			cnt = db.getCount("product p, brand b, wishlistmapping wm", "*",
					"p.isDisplay=1 AND p.idBrand=b.idBrand AND wm.idProduct=p.idProduct AND wm.idRegister=" + id);
		}
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = "";
		if (type.equals("category")) {
			sql = "SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.volume, p.price, p.ratingAvg, p.rationCount "
					+ "FROM product p, brand b, secondcategorymapping m "
					+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND m.idProduct=p.idProduct AND m.idSecondCategory=?" + where + " "
					+ "GROUP BY p.idProduct "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (type.equals("brand")) {
			sql = "SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.volume, p.price, p.ratingAvg, p.rationCount "
					+ "FROM product p, brand b, secondcategorymapping m, firstcategory f, secondcategory s "
					+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND m.idProduct=p.idProduct AND m.idSecondCategory=s.idSecondCategory AND "
					+ "s.idFirstCategory=f.idFirstCategory AND b.idBrand=?" + where + " "
					+ "GROUP BY p.idProduct "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (type.equals("search")) {
			sql = "SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.volume, p.price, p.ratingAvg, p.rationCount "
					+ "FROM product p, brand b, secondcategorymapping m "
					+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND m.idProduct=p.idProduct" + where + " "
					+ "GROUP BY p.idProduct "
					+ "ORDER BY " + order + " "
					+ "LIMIT ?, ?";
		} else if (type.equals("wish")) {
			sql = "SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.volume, p.price, p.ratingAvg, p.rationCount "
					+ "FROM product p, brand b, wishlistmapping wm "
					+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND wm.idProduct=p.idProduct AND wm.idRegister=? "
					+ "ORDER BY wm.create_date DESC "
					+ "LIMIT ?, ?";
		}
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			if (type.equals("search")) {
				pstmt.setInt(1, PageHandler.getInstance().getStartRec());
				pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			} else {
				pstmt.setInt(1, id);
				pstmt.setInt(2, PageHandler.getInstance().getStartRec());
				pstmt.setInt(3, PageHandler.getInstance().getPageArticleCount());
			}
			
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<ProductCMD>();
				
				while(rs.next()) {
					int idProduct = rs.getInt(1);
					String productTitle = rs.getString(2);
					int idBrand = rs.getInt(3);
					String brandTitle = rs.getString(4);
					String volume = rs.getString(5);
					int price = rs.getInt(6);
					double ratingAvg = rs.getDouble(7);
					int rationCount = rs.getInt(8);
					
					FileInfo productImg = this.getImage(idProduct, "product");
					FileInfo brandImg = this.getImage(idBrand, "brand");
					
					ProductCMD item = new ProductCMD(
							idProduct, idBrand, brandTitle, productTitle, volume, price, ratingAvg, rationCount, brandImg, productImg);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getProductList] " + e.getLocalizedMessage());
			logger.error("[getProductList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getProductList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getProductList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 제품 검색리스트 xml */
	public ArrayList<ProductCMD> getSearchProductList(int page, int count, String whereValue) throws SQLException {
		logger.debug("[getSearchProductList] ---------- Method Start ----------");
		
		ArrayList<ProductCMD> result = null;
		int cnt = db.getCount("(SELECT p.idProduct, p.productTitle, p.idBrand, p.volume, p.price "
				+ "FROM product p, brand b, secondcategorymapping m "
				+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND m.idProduct=p.idProduct" + whereValue + " "
				+ "GROUP BY p.idProduct) tbl", 
				"*", null);
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = "SELECT p.idProduct, p.productTitle, p.idBrand, p.volume, p.price "
				+ "FROM product p, brand b, secondcategorymapping m "
				+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND m.idProduct=p.idProduct" + whereValue + " "
				+ "GROUP BY p.idProduct "
				+ "ORDER BY p.productTitle "
				+ "LIMIT ?, ?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<ProductCMD>();
				
				while(rs.next()) {
					int idProduct = rs.getInt(1);
					String productTitle = rs.getString(2);
					int idBrand = rs.getInt(3);
					String volume = rs.getString(4);
					int price = rs.getInt(5);
					
					FileInfo productImg = this.getImage(idProduct, "product");
					FileInfo brandImg = this.getImage(idBrand, "brand");
					
					ProductCMD item = new ProductCMD(idProduct, idBrand, productTitle, volume, price, brandImg, productImg);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getSearchProductList] " + e.getLocalizedMessage());
			logger.error("[getSearchProductList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getSearchProductList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getSearchProductList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 제품 필터 브랜드 xml */
	public ArrayList<String> getProductFilterList(int id) throws SQLException {
		logger.debug("[getProductFilterList] ---------- Method Start ----------");
		
		ArrayList<String> result = null;
		
		String sql = "SELECT b.brandTitle "
					+ "FROM product p, brand b, secondcategorymapping m, secondcategory s "
					+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND m.idProduct=p.idProduct AND m.idSecondCategory=? "
					+ "GROUP BY b.idBrand ORDER BY b.brandTitle";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		
		try {
			pstmt.setInt(1, id);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<String>();
				
				while(rs.next()) {
					String brandTitle = rs.getString(1);
					result.add(brandTitle);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getProductFilterList] " + e.getLocalizedMessage());
			logger.error("[getProductFilterList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getProductFilterList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getProductFilterList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 제품 필터 키워드 xml */
	public String getProductFilterKeyWord(int id) throws SQLException {
		logger.debug("[getProductFilterKeyWord] ---------- Method Start ----------");
		
		String result = null;
		
		String sql = "SELECT keyWord "
					+ "FROM secondcategory "
					+ "WHERE idSecondCategory=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		
		try {
			pstmt.setInt(1, id);
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				if (rs.next()) {
					result = rs.getString(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getProductFilterKeyWord] " + e.getLocalizedMessage());
			logger.error("[getProductFilterKeyWord] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		logger.debug("[getProductFilterKeyWord] ---------- Method End ----------");
		
		return result;
	}
	
	/** 같은느낌 다른제품 리스트 xml */
	public ArrayList<ProductCMD> getProductList(int page, int count, int idProduct, String keyWord) throws SQLException {
		logger.debug("[getProductList] ---------- Method Start ----------");
		
		ArrayList<ProductCMD> result = new ArrayList<ProductCMD>();
		
		int secondCnt = DBHandler.getInstance().getCount("secondcategorymapping", "idSecondCategory", "idProduct=" + idProduct);
		int[] secondArr = new int[secondCnt];
		int secondTemp = 0;
		String secondList = "SELECT idSecondCategory FROM secondcategorymapping WHERE idProduct=?";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(secondList);
		try {
			pstmt.setInt(1, idProduct);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					secondArr[secondTemp] = rs.getInt(1);
					secondTemp++;
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getProductList] " + e.getLocalizedMessage());
			logger.error("[getProductList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		String se_cause = "(";
		for (int i=0; i<secondArr.length; i++) {
			se_cause += secondArr[i];
			if (i+1 <secondArr.length) {
				se_cause += ",";
			}
		}
		se_cause += ")";
		
		String[] keyWordArr = keyWord.split(",");
		if (keyWordArr == null || keyWordArr.length < 2) {
			return result;
		}
		
		String in_cause = "(";
		for (int i=0; i<keyWordArr.length; i++) {
			in_cause += "COUNT(case when p.keyWord like '%" + keyWordArr[i] + "%' then 1 end)";
			if (i+1 < keyWordArr.length) {
				in_cause += " + ";
			}
		}
		in_cause += ")";
		
		int cnt = db.getCount("(SELECT COUNT(p.idProduct) FROM product p, brand b, secondcategorymapping m WHERE p.isDisplay=1 AND "
				+ "p.idBrand=b.idBrand AND p.idProduct != " + idProduct	+ " AND p.idProduct=m.idProduct AND m.idSecondCategory in " + se_cause + " "
				+ "GROUP BY p.idProduct HAVING " + in_cause + " > 1) as sq", "*", null);
		
		if (count == -1) {
			count = cnt;
		}
		
		String sql = "SELECT p.idProduct, p.productTitle, p.idBrand, b.brandTitle, p.volume, p.price, p.ratingAvg, p.rationCount, " + in_cause + "cnt "
				+ "FROM product p, brand b, secondcategorymapping m "
				+ "WHERE p.isDisplay=1 AND p.idBrand=b.idBrand AND p.idProduct!=? AND p.idProduct=m.idProduct AND m.idSecondCategory in " + se_cause + " "
				+ "GROUP BY p.idProduct HAVING " + in_cause + " > 1 "
				+ "ORDER BY cnt DESC "
				+ "LIMIT ?, ?";
		
		pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, idProduct);
			pstmt.setInt(2, PageHandler.getInstance().getStartRec());
			pstmt.setInt(3, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				
				while(rs.next()) {
					int num = rs.getInt(1);
					String productTitle = rs.getString(2);
					int idBrand = rs.getInt(3);
					String brandTitle = rs.getString(4);
					String volume = rs.getString(5);
					int price = rs.getInt(6);
					double ratingAvg = rs.getDouble(7);
					int rationCount = rs.getInt(8);
					
					FileInfo productImg = this.getImage(num, "product");
					FileInfo brandImg = this.getImage(idBrand, "brand");
					
					ProductCMD item = new ProductCMD(
							num, idBrand, brandTitle, productTitle, volume, price, ratingAvg, rationCount, brandImg, productImg);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getProductList] " + e.getLocalizedMessage());
			logger.error("[getProductList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getProductList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getProductList] ---------- Method End ----------");
		
		return result;
	}
	
	/** 제품 요청 xml 
	 * @throws SQLException */
	public int addRequestProduct(int idRegister, String requestText, FileInfo image) throws SQLException {
		logger.debug("[addRequestProduct] ---------- Method Start ----------");
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		
		PreparedStatement pstmt = null;
		
		String productSql = "INSERT INTO pouchproductmapping ("
				+ "idPouch, idProduct, idRegister, requestText, isConfirm, create_date"
				+ ") VALUES (1, 1, ?, ?, 0, ?)";
		pstmt = db.getPrepareStatement(productSql);
		try {
			pstmt.setInt(1, idRegister);
			pstmt.setString(2, requestText);
			pstmt.setString(3, DateTime.getInstance().getNowDateTimeString());
			result = pstmt.executeUpdate();
			if (result > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[addRequestProduct] " + e.getLocalizedMessage());
			logger.error("[addRequestProduct] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (image != null) {
			String fileSql = "INSERT INTO attachefile ("
					+ "tableName, tableKey, fileOrgName, fileSaveName, fileDir, fileSize, fileType, isDisplay, create_date, isUser"
					+ ") VALUES ('pouchproductmapping', ?, ?, ?, ?, ?, ?, 1, ?, 0)";
			
			int fileResult = 0;
			
			pstmt = db.getPrepareStatement(fileSql);
			try {
				pstmt.setInt(1, result);
				pstmt.setString(2, image.getFileOrgName());
				pstmt.setString(3, image.getFileSaveName());
				pstmt.setString(4, image.getFileDir());
				pstmt.setLong(5, image.getFileSize());
				pstmt.setString(6, image.getFileType());
				pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
				
				fileResult = pstmt.executeUpdate();
				
				if (fileResult > 0) {
					// 자동증가 일련변호 조회
					ResultSet rs = pstmt.getGeneratedKeys();
					if (rs.next()) {
						fileResult = rs.getInt(1);
					}
					rs.close();
					rs = null;
				} else {
					// 모든 데이터 저장 취소
					pstmt.close();
					pstmt = null;
					DBHandler.getInstance().rollback();
				}
				pstmt.close();
				pstmt = null;
				
				String update = "UPDATE pouchproductmapping "
						+ "SET requestImg=? "
						+ "WHERE idPouchProduct=?";
				pstmt = db.getPrepareStatement(update);
				pstmt.setInt(1, fileResult);
				pstmt.setInt(2, result);
				pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {
				DBHandler.getInstance().rollback();
				logger.error("[addRequestProduct] " + e.getLocalizedMessage());
				logger.error("[addRequestProduct] " + e.getSQLState());
			} finally {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[addRequestProduct][RETURN] result=" + result);
		logger.debug("[addRequestProduct] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 이미지 리스트
	 * @param idx
	 * @return ArrayList<FileInfo>
	 */
	public ArrayList<FileInfo> getImageList(int idx) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType, sortKey "
				+ "FROM attachefile "
				+ "WHERE tableName='product' AND tableKey=? ORDER BY sortKey";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		ArrayList<FileInfo> imageList = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				imageList = new ArrayList<FileInfo>();
				
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					int sortKey = rs.getInt(7);
					
					FileInfo info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType, sortKey);
					imageList.add(info);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return imageList;
	}
	
	public FileInfo getImage(int idx, String table) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
				+ "FROM attachefile "
				+ "WHERE tableName='" + table + "' AND tableKey=? ORDER BY sortKey LIMIT 0, 1";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo info = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return info;
	}
	
}
